from typing import Any, Dict, List, Optional, Type, TypeVar, Generic, Union, Tuple
import json
from sqlalchemy import inspect, asc, desc, cast, Integer, Float, String
from app.models.database import  get_db, create_tables,Base
from app.services.db_service_interface import DBServiceInterface

from app.core.config import settings

T = TypeVar('T', bound=Base)

class SQLiteService(DBServiceInterface[T]):
    """通用SQLite服务，可以处理任何SQLAlchemy模型"""
    
    def __init__(self):
        print("初始化SQLite服务")
        create_tables()
        self.connected = True
    
    def save_entity(self, entity_class: Type[T], entity_data: Dict[str, Any], 
                   primary_key_value: Any = None, primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """保存任何实体到数据库
        
        Args:
            entity_class: 实体类
            entity_data: 实体数据
            primary_key_value: 主键值，用于查找现有实体
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            保存的实体对象的字典表示
        """
        db = get_db()
        
        try:
            # 处理复杂数据类型
            processed_data = {}
            for k, v in entity_data.items():
                if isinstance(v, (dict, list)):
                    processed_data[k] = json.dumps(v, ensure_ascii=False)
                else:
                    processed_data[k] = v
            
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            # 如果提供了主键值，查找现有实体
            entity = None
            if primary_key_value is not None:
                entity = db.query(entity_class).filter(
                    getattr(entity_class, primary_key_field) == primary_key_value
                ).first()
            
            if entity:
                # 更新现有实体
                for k, v in processed_data.items():
                    if hasattr(entity, k):
                        setattr(entity, k, v)
            else:
                # 创建新实体
                if primary_key_value is not None and primary_key_field in processed_data:
                    processed_data[primary_key_field] = primary_key_value
                entity = entity_class(**processed_data) 
                db.add(entity)
            
            
            db.commit()
            
            # 将实体转换为字典再返回
            result = {}
            for column in entity.__table__.columns:
                value = getattr(entity, column.name)
                # 尝试解析JSON字符串
                if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                    try:
                        value = json.loads(value)
                    except:
                        pass
                result[column.name] = value
            
            return result
        except Exception as e:
            print(f"保存实体到SQLite失败: {str(e)}")
            db.rollback()
            return None
    
    def save_entity_by_filter(self, entity_class: Type[T], entity_data: Dict[str, Any], 
                             filters: Dict[str, Any] = None) -> Optional[Dict[str, Any]]:
        """通过条件保存实体到数据库
        
        Args:
            entity_class: 实体类
            entity_data: 实体数据
            filters: 过滤条件，格式为 {字段名: 值}，用于查找现有实体
            
        Returns:
            保存的实体对象
        """
        db = get_db()
        
        try:
            # 处理复杂数据类型
            processed_data = {}
            for k, v in entity_data.items():
                if isinstance(v, (dict, list)):
                    processed_data[k] = json.dumps(v, ensure_ascii=False)
                else:
                    processed_data[k] = v
            
            # 查找现有实体
            entity = None
            if filters:
                query = db.query(entity_class)
                for field, value in filters.items():
                    if hasattr(entity_class, field):
                        query = query.filter(getattr(entity_class, field) == value)
                entity = query.first()
            
            if entity:
                # 更新现有实体
                for k, v in processed_data.items():
                    if hasattr(entity, k):
                        setattr(entity, k, v) 
            else:
                # 创建新实体
                # 将过滤条件合并到实体数据中
                if filters:
                    for field, value in filters.items():
                        if field not in processed_data and hasattr(entity_class, field):
                            processed_data[field] = value
                            
                entity = entity_class(**processed_data)
                db.add(entity)
                print(f"创建新实体: {entity}")
            
            db.commit()
            return entity
        except Exception as e:
            print(f"通过条件保存实体到SQLite失败: {str(e)}")
            db.rollback()
            return None
    
    
    def save_entities(self, entity_class: Type[T], entities_data: List[Dict[str, Any]],
                        primary_key_field: str = None) -> List[Optional[Dict[str, Any]]]:
            """批量保存多个实体到数据库
            
            Args:
                entity_class: 实体类
                entities_data: 实体数据列表，每个元素是一个字典
                primary_key_field: 主键字段名，如果为None则自动检测
                
            Returns:
                保存的实体对象的字典表示列表
            """
            db = get_db()
            results = []
            
            try:
                # 如果未指定主键字段，自动检测
                if primary_key_field is None:
                    mapper = inspect(entity_class)
                    primary_key_field = mapper.primary_key[0].name
                
                # 批量处理每个实体
                entities = []
                for entity_data in entities_data:
                    # 处理复杂数据类型
                    processed_data = {}
                    for k, v in entity_data.items():
                        if isinstance(v, (dict, list)):
                            processed_data[k] = json.dumps(v, ensure_ascii=False)
                        else:
                            processed_data[k] = v
                    
                    # 检查是否有主键值
                    primary_key_value = processed_data.get(primary_key_field)
                    
                    # 如果有主键值，查找现有实体
                    entity = None
                    if primary_key_value is not None:
                        entity = db.query(entity_class).filter(
                            getattr(entity_class, primary_key_field) == primary_key_value
                        ).first()
                    
                    if entity:
                        # 更新现有实体
                        for k, v in processed_data.items():
                            if hasattr(entity, k):
                                setattr(entity, k, v)
                    else:
                        # 创建新实体
                        entity = entity_class(**processed_data)
                        db.add(entity)
                    
                    entities.append(entity)
                
                # 提交事务
                db.commit()
                
                # 将所有实体转换为字典
                for entity in entities:
                    result = {}
                    for column in entity.__table__.columns:
                        value = getattr(entity, column.name)
                        # 尝试解析JSON字符串
                        if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                            try:
                                value = json.loads(value)
                            except:
                                pass
                        result[column.name] = value
                    results.append(result)
                
                return results
            except Exception as e:
                print(f"批量保存实体到SQLite失败: {str(e)}")
                db.rollback()
                return [None] * len(entities_data)
    
    
    def get_entity(self, entity_class: Type[T], primary_key_value: Any, 
                  primary_key_field: str = None) -> Optional[T]:
        """获取实体
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            实体对象或None
        """
        db = get_db()
        
        try:
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            entity = db.query(entity_class).filter(
                getattr(entity_class, primary_key_field) == primary_key_value
            ).first()
            
            return entity
        except Exception as e:
            print(f"获取实体失败: {str(e)}")
            return None
    
    def get_entity_as_dict(self, entity_class: Type[T], primary_key_value: Any, 
                          primary_key_field: str = None) -> Optional[Dict[str, Any]]:
        """获取实体并转换为字典
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            实体字典或None
        """
        entity = self.get_entity(entity_class, primary_key_value, primary_key_field)
        if not entity:
            return None
        
        # 将实体转换为字典
        result = {}
        for column in entity.__table__.columns:
            value = getattr(entity, column.name)
            # 尝试解析JSON字符串
            if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                try:
                    value = json.loads(value)
                except:
                    pass
            result[column.name] = value
        
        return result
    
    def delete_entity(self, entity_class: Type[T], primary_key_value: Any, 
                     primary_key_field: str = None) -> bool:
        """删除实体
        
        Args:
            entity_class: 实体类
            primary_key_value: 主键值
            primary_key_field: 主键字段名，如果为None则自动检测
            
        Returns:
            是否成功删除
        """
        db = get_db()
        
        try:
            # 如果未指定主键字段，自动检测
            if primary_key_field is None:
                mapper = inspect(entity_class)
                primary_key_field = mapper.primary_key[0].name
            
            entity = db.query(entity_class).filter(
                getattr(entity_class, primary_key_field) == primary_key_value
            ).first()
            
            if entity:
                db.delete(entity)
                db.commit()
                return True
            return False
        except Exception as e:
            print(f"删除实体失败: {str(e)}")
            db.rollback()
            return False
    
    def delete_entities_by_filter(self, entity_class: Type[T], filters: Dict[str, Any]) -> int:
        """按条件删除多个实体
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式为 {字段名: 值}
            
        Returns:
            删除的实体数量
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                for field, value in filters.items():
                    if hasattr(entity_class, field):
                        query = query.filter(getattr(entity_class, field) == value)
            
            # 获取符合条件的实体数量
            entities = query.all()
            count = len(entities)
            
            # 逐个删除实体，以触发级联删除
            for entity in entities:
                db.delete(entity)
            
            db.commit()
            
            return count
        except Exception as e:
            print(f"按条件删除实体失败: {str(e)}")
            db.rollback()
            return 0
    
    def _parse_order_by(self, entity_class: Type[T], order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]]) -> List:
        """解析排序参数
        
        Args:
            entity_class: 实体类
            order_by: 排序条件
            
        Returns:
            SQLAlchemy 排序表达式列表
        """
        if not order_by:
            return []
            
        order_expressions = []
        
        # 统一转换为列表格式
        if isinstance(order_by, str):
            order_list = [order_by]
        elif isinstance(order_by, tuple):
            order_list = [order_by]
        else:
            order_list = order_by
            
        for item in order_list:
            if isinstance(item, str):
                # 字符串格式，默认正序
                field_expression = item
                direction = "asc"
            elif isinstance(item, tuple) and len(item) == 2:
                # 元组格式 (字段表达式, 方向)
                field_expression, direction = item
            else:
                continue
                
            # 解析字段表达式，支持类型转换
            field_attr = self._parse_field_expression(entity_class, field_expression)
            if field_attr is None:
                continue
            
            # 应用排序方向
            if direction.lower() == "desc":
                order_expressions.append(desc(field_attr))
            else:  # 默认正序
                order_expressions.append(asc(field_attr))
                
        return order_expressions
    
    def _parse_field_expression(self, entity_class: Type[T], field_expression: str):
        """解析字段表达式，支持类型转换
        
        Args:
            entity_class: 实体类
            field_expression: 字段表达式，如 "field_name" 或 "field_name::int"
            
        Returns:
            SQLAlchemy 字段表达式或 None
        """
        # 检查是否包含类型转换
        if '::' in field_expression:
            field_name, cast_type = field_expression.split('::', 1)
            field_name = field_name.strip()
            cast_type = cast_type.strip().lower()
        else:
            field_name = field_expression.strip()
            cast_type = None
            
        # 检查字段是否存在
        if not hasattr(entity_class, field_name):
            return None
            
        field_attr = getattr(entity_class, field_name)
        
        # 应用类型转换
        if cast_type:
            if cast_type in ['int', 'integer']:
                field_attr = cast(field_attr, Integer)
            elif cast_type in ['float', 'real', 'numeric']:
                field_attr = cast(field_attr, Float)
            elif cast_type in ['str', 'string', 'text']:
                field_attr = cast(field_attr, String)
            # 可以根据需要添加更多类型转换
            
        return field_attr

    def _apply_filters(self, query, entity_class: Type[T], filters: Dict[str, Any]):
        """应用过滤条件到查询
        
        Args:
            query: SQLAlchemy 查询对象
            entity_class: 实体类
            filters: 过滤条件字典
            
        Returns:
            应用过滤条件后的查询对象
        """
        from sqlalchemy import or_, and_
        
        for field, condition in filters.items():
            # 处理特殊的 OR 条件
            if field == "_or":
                if isinstance(condition, list):
                    or_conditions = []
                    for or_filter in condition:
                        if isinstance(or_filter, dict):
                            for or_field, or_condition in or_filter.items():
                                if hasattr(entity_class, or_field):
                                    or_field_attr = getattr(entity_class, or_field)
                                    or_conditions.append(self._build_condition(or_field_attr, or_condition))
                    if or_conditions:
                        query = query.filter(or_(*or_conditions))
                continue
            
            # 处理关键词多字段搜索
            if field == "_keyword":
                if isinstance(condition, dict) and "value" in condition and "fields" in condition:
                    keyword = condition["value"]
                    search_fields = condition["fields"]
                    search_pattern = f"%{keyword}%"
                    
                    or_conditions = []
                    for search_field in search_fields:
                        if hasattr(entity_class, search_field):
                            field_attr = getattr(entity_class, search_field)
                            or_conditions.append(field_attr.like(search_pattern))
                    
                    if or_conditions:
                        query = query.filter(or_(*or_conditions))
                continue
            
            # 普通字段条件
            if not hasattr(entity_class, field):
                continue
                
            field_attr = getattr(entity_class, field)
            
            # 统一的条件处理：支持 logic 参数指定逻辑关系
            if isinstance(condition, dict) and "conditions" in condition:
                # 新的统一语法：{"conditions": [...], "logic": "and"|"or"}
                conditions_list = condition["conditions"]
                logic = condition.get("logic", "and").lower()
                
                if isinstance(conditions_list, list) and conditions_list:
                    condition_filters = []
                    for single_condition in conditions_list:
                        condition_filter = self._build_condition(field_attr, single_condition)
                        if condition_filter is not None:
                            condition_filters.append(condition_filter)
                    
                    if condition_filters:
                        if logic == "or":
                            query = query.filter(or_(*condition_filters))
                        else:  # 默认 and
                            query = query.filter(and_(*condition_filters))
            elif isinstance(condition, list):
                # 兼容旧语法：列表表示AND条件
                and_conditions = []
                for single_condition in condition:
                    condition_filter = self._build_condition(field_attr, single_condition)
                    if condition_filter is not None:
                        and_conditions.append(condition_filter)
                if and_conditions:
                    query = query.filter(and_(*and_conditions))
            else:
                # 单个条件
                condition_filter = self._build_condition(field_attr, condition)
                if condition_filter is not None:
                    query = query.filter(condition_filter)
                
        return query
    
    def _build_condition(self, field_attr, condition):
        """构建单个字段的查询条件"""
        if isinstance(condition, dict):
            # 复杂条件
            if "like" in condition:
                return field_attr.like(condition["like"])
            elif "ilike" in condition:
                # ILIKE 不区分大小写匹配 (SQLite 使用 LIKE 配合 COLLATE NOCASE)
                return field_attr.collate('NOCASE').like(condition["ilike"])
            elif "op" in condition and "value" in condition:
                op = condition["op"]
                value = condition["value"]
                if op == ">":
                    return field_attr > value
                elif op == ">=":
                    return field_attr >= value
                elif op == "<":
                    return field_attr < value
                elif op == "<=":
                    return field_attr <= value
                elif op == "!=":
                    return field_attr != value
                elif op == "in":
                    return field_attr.in_(value)
                elif op == "not_in":
                    return ~field_attr.in_(value)
                elif op == "is_null":
                    return field_attr.is_(None)
                elif op == "is_not_null":
                    return field_attr.isnot(None)
        else:
            # 简单的精确匹配
            return field_attr == condition
        
        return None

    def query_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                      limit: int = None, offset: int = None, 
                      order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[T]:
        """查询实体列表
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，支持以下格式：
                - 精确匹配: {字段名: 值}
                - LIKE 通配符: {字段名: {"like": "pattern"}}
                - ILIKE 不区分大小写: {字段名: {"ilike": "pattern"}}
                - 其他操作符: {字段名: {"op": ">", "value": 100}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
                - 类型转换: "field_name::int", ("field_name::float", "desc")
                  支持的类型: int/integer, float/real/numeric, str/string/text
            
        Returns:
            实体列表
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                query = self._apply_filters(query, entity_class, filters)
            
            # 应用排序
            order_expressions = self._parse_order_by(entity_class, order_by)
            for expr in order_expressions:
                query = query.order_by(expr)
            
            # 应用分页
            if offset is not None:
                query = query.offset(offset)
            if limit is not None:
                query = query.limit(limit)
            
            return query.all()
        except Exception as e:
            print(f"查询实体列表失败: {str(e)}")
            return []
    
    def query_entities_as_dicts(self, entity_class: Type[T], filters: Dict[str, Any] = None, 
                              limit: int = None, offset: int = None, 
                              order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> List[Dict[str, Any]]:
        """查询实体列表并转换为字典列表
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，支持以下格式：
                - 精确匹配: {字段名: 值}
                - LIKE 通配符: {字段名: {"like": "pattern"}}
                - ILIKE 不区分大小写: {字段名: {"ilike": "pattern"}}
                - 其他操作符: {字段名: {"op": ">", "value": 100}}
                - OR 条件: {"_or": [{"field1": "value1"}, {"field2": {"like": "pattern%"}}]}
                - 关键词多字段搜索: {"_keyword": {"value": "search_term", "fields": ["field1", "field2"]}}
            limit: 限制返回数量
            offset: 跳过记录数
            order_by: 排序条件，支持以下格式：
                - 字符串: "field_name" (默认正序)
                - 元组: ("field_name", "asc"|"desc")
                - 列表: [("field1", "asc"), ("field2", "desc"), "field3"]
                - 类型转换: "field_name::int", ("field_name::float", "desc")
                  支持的类型: int/integer, float/real/numeric, str/string/text
            
        Returns:
            字典列表
        """
        entities = self.query_entities(entity_class, filters, limit, offset, order_by)
        
        result = []
        for entity in entities:
            entity_dict = {}
            for column in entity.__table__.columns:
                value = getattr(entity, column.name)
                # 尝试解析JSON字符串
                if isinstance(value, str) and (value.startswith('{') or value.startswith('[')):
                    try:
                        value = json.loads(value)
                    except:
                        pass
                entity_dict[column.name] = value
            result.append(entity_dict)
        
        return result
    
    def count_entities(self, entity_class: Type[T], filters: Dict[str, Any] = None) -> int:
        """统计实体数量
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式与query_entities相同
            
        Returns:
            符合条件的实体数量
        """
        db = get_db()
        
        try:
            query = db.query(entity_class)
            
            # 应用过滤条件
            if filters:
                query = self._apply_filters(query, entity_class, filters)
            
            return query.count()
        except Exception as e:
            print(f"统计实体数量失败: {str(e)}")
            return 0
    
    def query_entities_as_dicts_by_page(self, entity_class: Type[T], filters: Dict[str, Any] = None,
                                       page: int = 1, pageSize: int = 10,
                                       order_by: Union[str, Tuple[str, str], List[Union[str, Tuple[str, str]]]] = None) -> Dict[str, Any]:
        """分页查询实体列表并返回完整分页信息
        
        Args:
            entity_class: 实体类
            filters: 过滤条件，格式与query_entities相同
            page: 页码，从1开始
            pageSize: 每页大小
            orderBy: 排序条件，格式与query_entities相同
            
        Returns:
            包含分页信息的字典：
            {
                "items": [...],        # 当前页数据
                "total": 100,          # 总记录数
                "page": 1,             # 当前页码
                "pageSize": 10,       # 每页大小
                "totalPages": 10      # 总页数
            }
        """
        # 确保页码和页大小有效
        page = max(1, page)
        pageSize = max(1, pageSize)
        
        # 计算偏移量
        offset = (page - 1) * pageSize
        
        # 获取总数
        total = self.count_entities(entity_class, filters)
        
        # 计算总页数
        total_pages = (total + pageSize - 1) // pageSize if total > 0 else 0
        
        # 获取当前页数据
        items = self.query_entities_as_dicts(entity_class, filters, pageSize, offset, order_by)
        
        return {
            "items": items,
            "total": total,
            "page": page,
            "pageSize": pageSize,
            "totalPages": total_pages
        }

    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            get_db()
            return True
        except Exception as e:
            print(f"SQLite连接测试失败: {str(e)}")
            return False


# 只有当DB_TYPE设置为sqlite时才创建实例
if settings.DB_TYPE.lower() == "sqlite":
    sqlite_service = SQLiteService()
    print(f"SQLite连接状态: {'成功' if sqlite_service.test_connection() else '失败'}")
else:
    # 创建一个空实例，避免导入错误
    sqlite_service = None


        

    